package salida;

import org.apache.poi.ss.util.CellReference;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;

/**
 *
 * @author chspi
 */
public class GeneraExcel {

  //Constantes que se usan para la generacion del archivo
  private static final String TITULO_ENCABEZADO1_AC1 = "CALCULO PARA DETERMINAR LA PROPORCION DE BONO ALCANZADO CON FUNCION EN LA META ALCANZADA (BD SIN EXCLUSIONES EXCEPTO DXP)";
  private static final String[] TITULOS_ENCABEZADO2_AC1 = {"TIPO", "ID AGENTE", "ID PROMOTORIA", "PROMOTORÍA ", "AP\nCOLECTIVO", "AP\nINDIVIDUAL", "AUTOMOVILES", "AVIACIÓN,\nMARITIMO Y\nTRANSP.",
    "DIVERSOS\nMISCELÁNEOS", "DIVERSOS\nTÉCNICOS", "GMM\nCOLECTIVO", "GMM\nINDIVIDUAL", "INCENDIO", "RESPONSABILIDAD\nCIVIL GENERAL",
    "TERREMOTO Y\nOTROS RIESGOS C.", "VIDA\nCOLECTIVO", "VIDA\nINDIVIDUAL", "VIDA\nGRUPO", "TOTAL GENERAL", "RECHAZOS", "META\nALCANZADA", "META DEL\nPERIODO",
    "% \nMETA ALCANZADA", "% BONO\nAUTOS", "% BONO\nDAÑOS", "% BONO\nPERSONAS", "% ALCANCE\nMETA AUTOS", "% ALCANCE\nMETA DAÑOS", "% ALCANCE\nMETA PERSONAS"};
  private static final String TITULO_ENCABEZADO1_AC2 = "BASE PARA CALCULO DE BONOS FINALES (BD CON EXCLUSIONES)";
  private static final String[] TITULOS_ENCABEZADO2_AC2 = {"TIPO", "ID AGENTE", "ID PROMOTORIA", "PROMOTORÍA ", "AP COLECTIVO", "AP INDIVIDUAL", "AUTOMOVILES", "AVIACIÓN,\nMARITIMO Y\nTRANSP.",
    "DIVERSOS\nMISCELÁNEOS", "DIVERSOS\nTÉCNICOS", "GMM\nCOLECTIVO", "GMM\nINDIVIDUAL", "INCENDIO", "RESPONSABILIDAD\nCIVIL GENERAL",
    "TERREMOTO Y\nOTROS RIESGOS C.", "VIDA\nCOLECTIVO", "VIDA\nINDIVIDUAL", "VIDA\nGRUPO", "TOTAL GENERAL"};
  private static final String TITULO_ENCABEZADO1_CALC = "CALCULO DE BONOS A PAGAR";
  private static final String[] TITULOS_ENCABEZADO2_CALC = {"TIPO", "ID AGENTE", "ID PROMOTORIA", "PROMOTORÍA ", "AP COLECTIVO", "AP INDIVIDUAL", "AUTOMOVILES", "AVIACIÓN,\nMARITIMO Y\nTRANSP.",
    "DIVERSOS\nMISCELÁNEOS", "DIVERSOS\nTÉCNICOS", "GMM\nCOLECTIVO", "GMM\nINDIVIDUAL", "INCENDIO", "RESPONSABILIDAD\nCIVIL GENERAL",
    "TERREMOTO Y\nOTROS RIESGOS C.", "VIDA\nCOLECTIVO", "VIDA\nINDIVIDUAL", "VIDA\nGRUPO", "TOTAL GENERAL"};
  //Utileria para la generacion del archivo
  CreationHelper createHelper;
  HSSFPalette pallette;
  Font fuenteUniversal;
  CellStyle estiloCeldasTexto;
  CellStyle estiloCeldasMoneda;
  CellStyle estiloCeldasNumero;
  CellStyle estiloCeldasPorcentaje;
  //Archivo que se generará
  HSSFWorkbook libroExcelGenerado;
  String fileName;

  public GeneraExcel(String fileName) {
    this.fileName = fileName;
  }

  public void inicializaExcel() {
    libroExcelGenerado = new HSSFWorkbook();
    createHelper = libroExcelGenerado.getCreationHelper();
    pallette = libroExcelGenerado.getCustomPalette();

    fuenteUniversal = libroExcelGenerado.createFont();
    fuenteUniversal.setFontHeightInPoints((short) 8);
    fuenteUniversal.setFontName("Calibri");

    //Se define el estilo para el texto
    estiloCeldasTexto = libroExcelGenerado.createCellStyle();
    estiloCeldasTexto.setFont(fuenteUniversal);
    estiloCeldasTexto.setAlignment(CellStyle.ALIGN_JUSTIFY);
    estiloCeldasTexto.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    //Se define el estilo para la moneda
    estiloCeldasMoneda = libroExcelGenerado.createCellStyle();
    estiloCeldasMoneda.setFont(fuenteUniversal);
    estiloCeldasMoneda.setAlignment(CellStyle.ALIGN_RIGHT);
    estiloCeldasMoneda.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    estiloCeldasMoneda.setDataFormat(libroExcelGenerado.createDataFormat().getFormat("$###,###,###.00"));

    //Se define el estilo para los numeros
    estiloCeldasNumero = libroExcelGenerado.createCellStyle();
    estiloCeldasNumero.setFont(fuenteUniversal);
    estiloCeldasNumero.setAlignment(CellStyle.ALIGN_CENTER);
    estiloCeldasNumero.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    //Se define el estilo para los porcentajes
    estiloCeldasPorcentaje = libroExcelGenerado.createCellStyle();
    estiloCeldasPorcentaje.setFont(fuenteUniversal);
    estiloCeldasPorcentaje.setAlignment(CellStyle.ALIGN_RIGHT);
    estiloCeldasPorcentaje.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    estiloCeldasPorcentaje.setDataFormat(libroExcelGenerado.createDataFormat().getFormat("0.0%"));

    inicializaHojaAcumulado1();
    inicializaHojaAcumulado2();
  }

  public void generaExcel() throws IOException {
    FileOutputStream fileOut = new FileOutputStream(fileName);
    libroExcelGenerado.write(fileOut);
    fileOut.close();
  }

  public void inicializaHojaAcumulado1() {
    //Se agrega la hoja para el acumulado 1
    Sheet hojaAcumulado1 = libroExcelGenerado.createSheet("Acumulado 1");

    //ENCABEZADOS: Se compone de 2 renglones (encabezados)
    //ENCABEZADO 1
    Row renglonEncabezado1 = hojaAcumulado1.createRow(0);
    renglonEncabezado1.setHeightInPoints(15);
    {
      //Se crea el estilo de celdas del encabezado 1
      Font font = libroExcelGenerado.createFont();
      font.setFontHeightInPoints((short) 11);
      font.setFontName("Calibri");
      font.setColor(IndexedColors.WHITE.getIndex());
      font.setBoldweight(Font.BOLDWEIGHT_BOLD);

      CellStyle estiloCeldasEncabezado1 = libroExcelGenerado.createCellStyle();
      estiloCeldasEncabezado1.setFont(font);
      estiloCeldasEncabezado1.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
      estiloCeldasEncabezado1.setFillPattern(CellStyle.SOLID_FOREGROUND);
      estiloCeldasEncabezado1.setAlignment(CellStyle.ALIGN_CENTER);
      estiloCeldasEncabezado1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

      //Se vacia su contenido
      Cell celdaTituloEncabezado1 = renglonEncabezado1.createCell(convierteColAIndex("A"));
      celdaTituloEncabezado1.setCellValue(createHelper.createRichTextString(TITULO_ENCABEZADO1_AC1));
      celdaTituloEncabezado1.setCellStyle(estiloCeldasEncabezado1);
      mergeCeldas(hojaAcumulado1, 0, 0, "A", "AC");
      hojaAcumulado1.autoSizeColumn(celdaTituloEncabezado1.getColumnIndex());
    }

    //ENCABEZADO 2
    Row renglonEncabezado2 = hojaAcumulado1.createRow(1);
    renglonEncabezado2.setHeightInPoints(41);
    {

      //Se crea el estilo de celdas del encabezado 2
      Font font = libroExcelGenerado.createFont();
      font.setFontHeightInPoints((short) 8);
      font.setFontName("Calibri");
      font.setColor(IndexedColors.BLACK.getIndex());
      font.setBoldweight(Font.BOLDWEIGHT_BOLD);

      CellStyle estiloCeldasEncabezado2 = libroExcelGenerado.createCellStyle();
      estiloCeldasEncabezado2.setFont(font);
      estiloCeldasEncabezado2.setAlignment(CellStyle.ALIGN_CENTER);
      estiloCeldasEncabezado2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      estiloCeldasEncabezado2.setWrapText(true);

      for (int i = 0; i < TITULOS_ENCABEZADO2_AC1.length; i++) {
        Cell celdaI = renglonEncabezado2.createCell(i);
        celdaI.setCellValue(createHelper.createRichTextString(TITULOS_ENCABEZADO2_AC1[i]));
        celdaI.setCellStyle(estiloCeldasEncabezado2);
        hojaAcumulado1.autoSizeColumn(celdaI.getColumnIndex());
      }
    }
  }

  public void inicializaHojaAcumulado2() {
    //Se agrega la hoja para el acumulado 2
    Sheet hojaAcumulado2 = libroExcelGenerado.createSheet("Acumulado 2");

    //ENCABEZADOS: Se compone de 2 renglones (encabezados)
    //ENCABEZADO 1
    Row renglonEncabezado1 = hojaAcumulado2.createRow(0);
    renglonEncabezado1.setHeightInPoints(15);
    {
      //Se crea el estilo de celdas del encabezado 1
      Font fontENC = libroExcelGenerado.createFont();
      fontENC.setFontHeightInPoints((short) 11);
      fontENC.setFontName("Calibri");
      fontENC.setColor(IndexedColors.WHITE.getIndex());
      fontENC.setBoldweight(Font.BOLDWEIGHT_BOLD);

      CellStyle estiloCeldasEncabezado1 = libroExcelGenerado.createCellStyle();
      estiloCeldasEncabezado1.setFont(fontENC);
      estiloCeldasEncabezado1.setFillForegroundColor(IndexedColors.BLACK.getIndex());
      estiloCeldasEncabezado1.setFillPattern(CellStyle.SOLID_FOREGROUND);
      estiloCeldasEncabezado1.setAlignment(CellStyle.ALIGN_CENTER);
      estiloCeldasEncabezado1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

      //Se vacia su contenido
      Cell celdaTituloEncabezado1 = renglonEncabezado1.createCell(convierteColAIndex("A"));
      celdaTituloEncabezado1.setCellValue(createHelper.createRichTextString(TITULO_ENCABEZADO1_AC2));
      celdaTituloEncabezado1.setCellStyle(estiloCeldasEncabezado1);
      mergeCeldas(hojaAcumulado2, 0, 0, "A", "S");
      hojaAcumulado2.autoSizeColumn(celdaTituloEncabezado1.getColumnIndex());
    }

    //ENCABEZADO 2
    Row renglonEncabezado2 = hojaAcumulado2.createRow(1);
    renglonEncabezado2.setHeightInPoints(41);
    {
      //Se crea el estilo de celdas del encabezado 2
      Font font = libroExcelGenerado.createFont();
      font.setFontHeightInPoints((short) 8);
      font.setFontName("Calibri");
      font.setColor(IndexedColors.BLACK.getIndex());
      font.setBoldweight(Font.BOLDWEIGHT_BOLD);

      CellStyle estiloCeldasEncabezado2P1 = libroExcelGenerado.createCellStyle();
      estiloCeldasEncabezado2P1.setFont(font);
      estiloCeldasEncabezado2P1.setAlignment(CellStyle.ALIGN_CENTER);
      estiloCeldasEncabezado2P1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      estiloCeldasEncabezado2P1.setWrapText(true);

      for (int i = 0; i < TITULOS_ENCABEZADO2_AC2.length; i++) {
        Cell celdaI = renglonEncabezado2.createCell(i);
        celdaI.setCellValue(createHelper.createRichTextString(TITULOS_ENCABEZADO2_AC2[i]));
        celdaI.setCellStyle(estiloCeldasEncabezado2P1);
        hojaAcumulado2.autoSizeColumn(celdaI.getColumnIndex());
      }
    }

  }

  public void inicializaHojaCalculo() {
    //Se agrega la hoja para el calculo
    Sheet hojaCalculo = libroExcelGenerado.createSheet("Bono");

    //ENCABEZADOS: Se compone de 2 renglones (encabezados)
    //ENCABEZADO 1
    Row renglonEncabezado1 = hojaCalculo.createRow(0);
    renglonEncabezado1.setHeightInPoints(15);
    {
      //Se crea el estilo de celdas del encabezado 1
      Font fontENC = libroExcelGenerado.createFont();
      fontENC.setFontHeightInPoints((short) 11);
      fontENC.setFontName("Calibri");
      fontENC.setColor(IndexedColors.BLACK.getIndex());
      fontENC.setBoldweight(Font.BOLDWEIGHT_BOLD);
      CellStyle estiloCeldasEncabezado1 = libroExcelGenerado.createCellStyle();
      estiloCeldasEncabezado1.setFont(fontENC);
      estiloCeldasEncabezado1.setFillForegroundColor(IndexedColors.RED.getIndex());
      estiloCeldasEncabezado1.setFillPattern(CellStyle.SOLID_FOREGROUND);
      estiloCeldasEncabezado1.setAlignment(CellStyle.ALIGN_CENTER);
      estiloCeldasEncabezado1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

      //Se vacia su contenido
      Cell celdaTituloEncabezado1 = renglonEncabezado1.createCell(convierteColAIndex("A"));
      celdaTituloEncabezado1.setCellValue(createHelper.createRichTextString(TITULO_ENCABEZADO1_CALC));
      celdaTituloEncabezado1.setCellStyle(estiloCeldasEncabezado1);
      mergeCeldas(hojaCalculo, 0, 0, "A", "S");
      hojaCalculo.autoSizeColumn(celdaTituloEncabezado1.getColumnIndex());
    }

    //ENCABEZADO 2
    Row renglonEncabezado2 = hojaCalculo.createRow(1);
    renglonEncabezado2.setHeightInPoints(41);
    {
      //Se crea el estilo de celdas del encabezado 2
      Font font = libroExcelGenerado.createFont();
      font.setFontHeightInPoints((short) 8);
      font.setFontName("Calibri");
      font.setColor(IndexedColors.BLACK.getIndex());
      font.setBoldweight(Font.BOLDWEIGHT_BOLD);

      CellStyle estiloCeldasEncabezado2P1 = libroExcelGenerado.createCellStyle();
      estiloCeldasEncabezado2P1.setFont(font);
      estiloCeldasEncabezado2P1.setAlignment(CellStyle.ALIGN_CENTER);
      estiloCeldasEncabezado2P1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      estiloCeldasEncabezado2P1.setWrapText(true);

      for (int i = 0; i < TITULOS_ENCABEZADO2_CALC.length; i++) {
        Cell celdaI = renglonEncabezado2.createCell(i);
        celdaI.setCellValue(createHelper.createRichTextString(TITULOS_ENCABEZADO2_CALC[i]));
        celdaI.setCellStyle(estiloCeldasEncabezado2P1);
        hojaCalculo.autoSizeColumn(celdaI.getColumnIndex());
      }
    }

  }

  public void llenaHojaCalculo() {

    Sheet hojaAcumulado1 = libroExcelGenerado.getSheetAt(0);
    Sheet hojaAcumulado2 = libroExcelGenerado.getSheetAt(1);
    Sheet hojaCalculo = libroExcelGenerado.getSheetAt(2);

    if (hojaAcumulado2 != null && hojaAcumulado1 != null) {
      for (int i = 2; i <= hojaAcumulado2.getLastRowNum(); i++) {
        Row renglonActualAc2 = hojaAcumulado2.getRow(i);
        Row renglonActualAc1 = hojaAcumulado1.getRow(i);
        Row nuevoRenglon = hojaCalculo.createRow(i);
        double sumaActual = 0;

        for (int j = 0; j < renglonActualAc2.getLastCellNum() - 1; hojaCalculo.autoSizeColumn(j), j++) {
          Cell celdaActual = renglonActualAc2.getCell(j);
          Cell nuevaCelda = nuevoRenglon.createCell(j);

          if (celdaActual == null) {
            nuevaCelda.setCellValue((double) 0);
            nuevaCelda.setCellStyle(estiloCeldasMoneda);
            continue;
          }

          if (celdaActual.getCellType() == Cell.CELL_TYPE_NUMERIC
                  && j >= convierteColAIndex("E")) {
            int dañoVidaPersona = -1;
            int indexColumna = celdaActual.getColumnIndex() - 3;
            if (indexColumna == 4 || indexColumna == 5 || indexColumna == 6
                    || indexColumna == 9 || indexColumna == 10 || indexColumna == 11) {
              dañoVidaPersona = 0;
            } else if (indexColumna == 1 || indexColumna == 2 || indexColumna == 7
                    || indexColumna == 8 || indexColumna == 12 || indexColumna == 13 || indexColumna == 14) {
              dañoVidaPersona = 1;
            } else if (indexColumna == 3) {
              dañoVidaPersona = 2;
            }

            if (dañoVidaPersona != -1) {

              double valActual = celdaActual.getNumericCellValue();
              double porcActual;
              if (renglonActualAc1.getCell(convierteColAIndex("AB")).getCellType() == Cell.CELL_TYPE_ERROR) {
                renglonActualAc1.getCell(convierteColAIndex("AB")).setCellValue(0);
                porcActual = 0;
              }
              if (renglonActualAc1.getCell(convierteColAIndex("AC")).getCellType() == Cell.CELL_TYPE_ERROR) {
                renglonActualAc1.getCell(convierteColAIndex("AC")).setCellValue(0);
                porcActual = 0;
              }
              if (renglonActualAc1.getCell(convierteColAIndex("AA")).getCellType() == Cell.CELL_TYPE_ERROR) {
                renglonActualAc1.getCell(convierteColAIndex("AA")).setCellValue(0);
                porcActual = 0;
              }

              double multipl = 0;
              nuevaCelda.setCellStyle(estiloCeldasMoneda);
              switch (dañoVidaPersona) {
                case 0:
                  multipl = valActual * renglonActualAc1.getCell(convierteColAIndex("AB")).getNumericCellValue();
                  sumaActual = sumaActual + multipl;
                  nuevaCelda.setCellValue(multipl);
                  break;
                case 1:
                  multipl = valActual * renglonActualAc1.getCell(convierteColAIndex("AC")).getNumericCellValue();
                  sumaActual = sumaActual + multipl;
                  nuevaCelda.setCellValue(multipl);
                  break;
                case 2:
                  multipl = valActual * renglonActualAc1.getCell(convierteColAIndex("AA")).getNumericCellValue();
                  sumaActual = sumaActual + multipl;
                  nuevaCelda.setCellValue(multipl);
                  break;
              }
            }
          } else {
            if (celdaActual.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              nuevaCelda.setCellValue(celdaActual.getNumericCellValue());
              nuevaCelda.setCellStyle(estiloCeldasNumero);
            }
          }
          if (celdaActual.getCellType() == Cell.CELL_TYPE_STRING) {
            nuevaCelda.setCellValue(createHelper.createRichTextString(celdaActual.getStringCellValue()));
            nuevaCelda.setCellStyle(estiloCeldasTexto);
          }
        }
        Cell celdaSuma = nuevoRenglon.createCell(nuevoRenglon.getLastCellNum());
        celdaSuma.setCellValue(sumaActual);
        celdaSuma.setCellStyle(estiloCeldasMoneda);
      }
    }
  }

  public void setFileName(String newFileName) {
    this.fileName = newFileName;
  }

  private int convierteColAIndex(String columnaEnLetras) {
    return CellReference.convertColStringToIndex(columnaEnLetras);
  }

  private void mergeCeldas(Sheet hoja, int idReng1, int idReng2, String idCol1, String idCol2) {
    hoja.addMergedRegion(new CellRangeAddress(idReng1, idReng2, convierteColAIndex(idCol1), convierteColAIndex(idCol2)));
  }

  public void insertaRenglonAc1(String[] datosRenglon) {
    Sheet hojaAc1 = libroExcelGenerado.getSheetAt(0);

    if (hojaAc1 != null) {
      Row renglonActual = hojaAc1.createRow(hojaAc1.getLastRowNum() + 1);
      for (int i = 0; i < datosRenglon.length; i++) {
        if (datosRenglon[i] != null) {
          boolean esNumero = true;
          Cell celdaActual = renglonActual.createCell(i);
          try {
            Double.parseDouble(datosRenglon[i]);
          } catch (NumberFormatException nfe) {
            esNumero = false;
          }

          if (!esNumero) {
            celdaActual.setCellStyle(estiloCeldasTexto);
            celdaActual.setCellValue(datosRenglon[i]);
          } else {
            celdaActual.setCellValue(Double.parseDouble(datosRenglon[i]));
            if (i >= convierteColAIndex("W") && i <= convierteColAIndex("AC")) {
              celdaActual.setCellStyle(estiloCeldasPorcentaje);
            } else if (i == convierteColAIndex("B") || i == convierteColAIndex("C")) {
              celdaActual.setCellStyle(estiloCeldasNumero);
            } else {
              celdaActual.setCellStyle(estiloCeldasMoneda);
            }
          }
          hojaAc1.autoSizeColumn(i);
        }
      }
    }
  }

  public void insertaRenglonAc2(String[] datosRenglon) {
    Sheet hojaAc2 = libroExcelGenerado.getSheetAt(1);
    if (hojaAc2 != null) {
      Row renglonActual = hojaAc2.createRow(hojaAc2.getLastRowNum() + 1);
      for (int i = 0; i < convierteColAIndex("T"); i++) {
        if (datosRenglon[i] != null) {
          boolean esNumero = true;
          Cell celdaActual = renglonActual.createCell(i);
          try {
            Double.parseDouble(datosRenglon[i]);
          } catch (NumberFormatException nfe) {
            esNumero = false;
          }

          if (!esNumero) {
            celdaActual.setCellStyle(estiloCeldasTexto);
            celdaActual.setCellValue(datosRenglon[i]);
          } else {
            celdaActual.setCellValue(Double.parseDouble(datosRenglon[i]));
            if (i == convierteColAIndex("B") || i == convierteColAIndex("C")) {
              celdaActual.setCellStyle(estiloCeldasNumero);
            } else {
              celdaActual.setCellStyle(estiloCeldasMoneda);
            }
          }
        }
        hojaAc2.autoSizeColumn(i);
      }
    }
  }
}
